package com.softwareweb.dao;

import java.sql.*;

import com.softwareweb.model.UserInfo;


/**
 * 文件名：ConnDB
 *  描述：操作数据库
 *  公司：
 *  作者：liangfengsid
 */
public class ConnDB {
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	String url="jdbc:mysql://localhost:3306/software?useUnicode=true&characterEncoding=UTF-8";
	String user="root";
	String pass="tylercool900202";
	PreparedStatement pstmt =null;
	
	public ConnDB() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url,user,pass);
		} catch (Exception e) {
			e.printStackTrace(System.err);
			System.out.print("error ends here!\n");
		}
	}

	/**
	 * 功能描述： 通过Statement对象执行查询操作
	 * 参数：  String
	 * 返回值： ResultSet
	*/
	public ResultSet executeQuery(String sql) {
		try {
			//stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
			//		ResultSet.CONCUR_READ_ONLY);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return rs;
	}

	/**
	 * 功能描述： 通过PreparedStatement对象执行查询操作
	 * 参数：  String
	 * 返回值： ResultSet
	*/
	public ResultSet executeQueryPS(String userName) {
		try {
			String sql="";
			if(userName==null || userName.equals("")){
				sql="SELECT * FROM tb_order";
				pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			}else{
				sql="SELECT * FROM tb_order WHERE username=?";
				pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				pstmt.setString(1,userName);
			}
			rs = pstmt.executeQuery();
		} catch (SQLException ex) {
			System.err.println(ex);
		}
		return rs;
	}	
	
	/**
	 * 功能描述： 执行更新操作
	 * 参数：  String
	 * 返回值： int
	*/
	public int executeUpdate(String sql) {
		int result = 0;
		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			result = stmt.executeUpdate(sql);
		} catch (SQLException ex) {
			result = 0;
		}
		return result;
	}

	/**
	 * 功能描述： 关闭数据库连接
	 * 参数：  无
	 * 返回值： void
	 */	
	public void close() {
		try {
			if (rs != null)
				rs.close();
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		try {
			if (stmt != null)
				stmt.close();
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (Exception e) {
			e.printStackTrace(System.err);
		}
	}
	
	public int insertUser(UserInfo user){
		String insertCmd="insert into user (name,password,email,mobile,phone,QQ,MSN,address," +
				"city,photo,occupation,introduction,account,moneygot,priority,companytype,numfinished," +
				"score,goldpoint,registerdate,safequestion,safeanswer) " +
				"values('"+user.getName()+"','"+user.getPass()+"','"+user.getEmail()+"','"+user.getMobile()+"','"
				+user.getPhone()+"','"+user.getQQ()+"','"+user.getMSN()+"','"+user.getAddress()+"','"+user.getCity()
				+"','"+user.getPhotoURI()+"','"+user.getOccupation()+"','"+user.getIntroduction()+"',"+user.getAccount()
				+","+user.getMoneyGot()+","+user.getPriority()+","+user.getCompanyType()+","+user.getNumFinished()
				+","+user.getScore()+","+user.getGoldPoint()+",'"+user.getRegisterDate()+"','"+user.getSafeQuestion()
				+"','"+user.getSafeAnswer()+"')";
		System.out.println(insertCmd);
		int result=this.executeUpdate(insertCmd);
		return result;
	}

}
